package com.szholly.data.jdbc.oracle;

import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.szholly.utils.spring.SpringBeanFactory;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import com.szholly.utils.util.StringUtils;
import com.szholly.data.common.delegate.DataReaderDelegate;
import com.szholly.data.general.*;
import com.szholly.data.general.configuration.DefulatConfig;
import com.szholly.data.jdbc.DatabaseWorkspaceBase;

/**
 * 定义Oracle数据库工作空间
 */
@Component("Workspace_DB_Oracle")
@Scope("prototype")
public class OracleWorkspaceClass extends DatabaseWorkspaceBase {

	/**
	 * 表自动增值字段获取条件
	 */
	private static Map<String, String> tableAutoFieldValueCache = new HashMap<String, String>();

	public OracleWorkspaceClass(ConnectionInfo info) {
		super(info);
	}

	@Override
	protected BasicDataSource getDataSource(ConnectionInfo info) {
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		if (info.getPort().endsWith("/")) {
			// Oracle RAC连接
			dataSource.setUrl("jdbc:oracle:thin:@" + info.getServer() + ":"
					+ info.getPort() + info.getDatabase());
		} else {
			// 普通连接
			dataSource.setUrl("jdbc:oracle:thin:@" + info.getServer() + ":"
					+ info.getPort() + ":" + info.getDatabase());
		}
		dataSource.setUsername(info.getUserID());
		dataSource.setPassword(info.getPassword());
		// 连接池启动时的初始值
		dataSource.setInitialSize(1);
		// 连接池的最大值
		dataSource.setMaxActive(300);
		// 最大空闲值
		dataSource.setMaxIdle(1);
		// 最小空闲值
		dataSource.setMinIdle(0);
		return dataSource;
	}

	@Override
	protected String replaceSql(String sqlString) {
		if (StringUtils.IsNullOrSpace(sqlString))
			return sqlString;

		sqlString = sqlString.replace("[#COMBSTR#]", "||");
		sqlString = sqlString.replace("[", "").replace("]", "")
				.replace("@", ":").replace("#BDATE", "to_date('")
				.replace("#EDATE", "','yyyy-mm-dd')")
				.replace("#EDATETIME", "','yyyy-mm-dd hh24:mi:ss')")
				.replace("#SYSDATETIME#", "sysdate");

		return sqlString;
	}

	@Override
	public boolean fieldExist(String name, String fieldName) {
		name = name.trim().toUpperCase();
		fieldName = fieldName.trim().toUpperCase();

		String sql = "select count(COLUMN_NAME) from USER_TAB_COLUMNS where TABLE_NAME=@TABLE_NAME and COLUMN_NAME=@COLUMN_NAME";
		GlobalParameter[] para = { new GlobalParameter("@TABLE_NAME", name),
				new GlobalParameter("@COLUMN_NAME", fieldName) };

		Object obj = getExecuteScalar(sql, para);
		long cmdresult;

		if (obj == null)
			cmdresult = 0;
		else
			cmdresult = Long.parseLong(obj.toString());
		if (cmdresult == 0)
			return false;
		else
			return true;
	}

	/**
	 * 获取得到一个表的自动增值SQL
	 */
	@Override
	public String getAutoSql(ITable table, String fieldName) {
		String autoSql = getAutoFieldValueSql(table.getTableName(), fieldName);
		return autoSql;
	}

	/**
	 * 获取指定自动增值列的值获取SQL语句
	 */
	protected String getAutoFieldValueSql(String tableName, String fieldName) {
		if (tableName == null)
			return "";

		if (tableAutoFieldValueCache.containsKey(tableName))
			return tableAutoFieldValueCache.get(tableName);
		else {
			String autoFieldValue = "";
			createSequence(tableName, fieldName);
			autoFieldValue = tableName.toUpperCase().trim() + "_SE.nextval";
			tableAutoFieldValueCache.put(tableName, autoFieldValue);
			return autoFieldValue;
		}
	}

	/**
	 * 给指定的表创建序列，如果序列不存在
	 */
	private void createSequence(String tableName, String fieldName) {
		String sequenceName = tableName.toUpperCase().trim() + "_SE";

		String sql = "SELECT COUNT(*) FROM ALL_SEQUENCES T WHERE T.SEQUENCE_OWNER='"
				+ getUserID().toUpperCase()
				+ "' AND T.SEQUENCE_NAME='"
				+ sequenceName + "' ";

		Object obj = getExecuteScalar(sql, GlobalParameter.Empty);
		if (obj == null || Integer.parseInt(obj.toString()) <= 0) {
			sql = "CREATE SEQUENCE {0} MINVALUE 1 MAXVALUE 2147483647 START WITH {1} INCREMENT BY 1 NOCACHE ";
			String maxSql = " select max(" + fieldName + ") from " + tableName;
			Object maxLong = this.getExecuteScalar(maxSql,
					GlobalParameter.Empty);
			long max = 0;
			if (maxLong != null) {
				maxLong = Long.parseLong(maxLong.toString());
			}
			if (max <= 0)
				max = 1;
			else
				max++;
			sql = MessageFormat.format(sql, sequenceName, max);
			executeSql(sql, GlobalParameter.Empty);
		}
	}

	@Override
	public ITableInfo getTableMetadate(String objName) {
		if (StringUtils.IsNullOrSpace(objName))
			return null;

		objName = objName.trim().toUpperCase();
		TableType tableType = getObjectType(objName);
		if (tableType == TableType.UnDefine)
			return null;

		TableInfoClass objMetadata = null;
		switch (tableType) {
		case Table:
			objMetadata = getTableMetadata(objName);
			break;
		case View:
			objMetadata = getViewMetadate(objName);
			break;
		case UnDefine:
		default:
			break;
		}
		return objMetadata;
	}

	/**
	 * 获取表的元数据
	 * 
	 * @param tableName
	 * @return
	 */
	protected TableInfoClass getTableMetadata(String tableName) {
		String sql = " select  COL.COLUMN_ID as colorder, "
				+ " COL.COLUMN_NAME as ColumnName,"
				+ " COL.DATA_TYPE as TypeName, "
				+ " DECODE(COL.DATA_TYPE, 'NUMBER',COL.DATA_PRECISION, COL.DATA_LENGTH) as Length,"
				+ " COL.DATA_PRECISION as Preci,"
				+ " COL.DATA_SCALE as Scale,"
				+ " '' as IsIdentity,"
				+ " case when PKCOL.COLUMN_POSITION >0  then 'True'else 'False' end as isPK,"
				+ " case when COL.NULLABLE='Y'  then 'True'else 'False' end as cisNull, "
				+ " COL.DATA_DEFAULT as defaultVal, "
				+ " CCOM.COMMENTS as deText,"
				+ " COL.NUM_DISTINCT as NUM_DISTINCT "
				+ " FROM USER_TAB_COLUMNS COL,USER_COL_COMMENTS CCOM, "
				+ " ( SELECT AA.TABLE_NAME, AA.INDEX_NAME, AA.COLUMN_NAME, AA.COLUMN_POSITION"
				+ " FROM USER_IND_COLUMNS AA, USER_CONSTRAINTS BB"
				+ " WHERE BB.CONSTRAINT_TYPE = 'P'"
				+ " AND AA.TABLE_NAME = BB.TABLE_NAME"
				+ " AND AA.INDEX_NAME = BB.CONSTRAINT_NAME"
				+ " AND AA.TABLE_NAME = @tableName1  "
				+ " ) PKCOL WHERE COL.TABLE_NAME = CCOM.TABLE_NAME"
				+ " AND COL.COLUMN_NAME = CCOM.COLUMN_NAME"
				+ " AND COL.TABLE_NAME = @tableName2  "
				+ " AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+)"
				+ " AND COL.TABLE_NAME = PKCOL.TABLE_NAME(+)"
				+ " ORDER BY COL.COLUMN_ID ";

		GlobalParameter[] para = {
				new GlobalParameter("@tableName1", tableName),
				new GlobalParameter("@tableName2", tableName) };
		List<RowBase> schemaTable = null;
		schemaTable = getEntityList(RowBase.class, sql, para);

		IDataTypeConvert dataTypeConvert = new OracleDataTypeConvert();
		HashMap<String, Field> fields = new HashMap<String, Field>();

		if (schemaTable != null && schemaTable.size() > 0) {
			for (RowBase row : schemaTable) {
				Field field = new Field();
				field.setFieldName((String) DbValueConvertUtils.GetRowObject(
						row, "COLUMNNAME"));
				field.setFieldID(StringUtils.objectToInt(DbValueConvertUtils
						.GetRowObject(row, "COLORDER")));
				field.setAllowNull(StringUtils.getBool(DbValueConvertUtils
						.GetRowObject(row, "CISNULL").toString()));
				field.setDataType(dataTypeConvert.Convert(row.getValue(
						"TYPENAME").toString()));
				if (field.getDataType() == GlobalDataType.NoDefault)
					field.setCanShow(false);
				else {
					if (RowBase.isSystemField(field.getFieldName()))
						field.setCanShow(false);
					else
						field.setCanShow(true);
				}

				if (null == DbValueConvertUtils.GetRowObject(row, "SCALE")) {
					field.setDecimaldigits(0);
				} else {
					field.setDecimaldigits(StringUtils
							.objectToInt(DbValueConvertUtils.GetRowObject(row,
									"SCALE")));
				}

				if (null == DbValueConvertUtils.GetRowObject(row, "DEFAULTVAL")) {
					field.setDefaultValue("");
				} else {
					field.setDefaultValue((String) DbValueConvertUtils
							.GetRowObject(row, "DEFAULTVAL"));
				}
				
				if (null == DbValueConvertUtils.GetRowObject(row, "DETEXT")) {
					field.setFieldAliasName(field.getFieldName());
				} else {
					field.setFieldAliasName((String) DbValueConvertUtils
							.GetRowObject(row, "DETEXT"));
				}
				
				if (null == DbValueConvertUtils.GetRowObject(row, "ISIDENTITY")) {
					field.setIsAuto(false);
				} else {
					field.setIsAuto(StringUtils.getBool(DbValueConvertUtils
							.GetRowObject(row, "ISIDENTITY").toString()));
				}
				field.setIsKey(StringUtils.getBool(DbValueConvertUtils
						.GetRowObject(row, "ISPK").toString()));
				if (null == DbValueConvertUtils.GetRowObject(row, "LENGTH"))
					field.setMaxLen(0);
				else {
					field.setMaxLen(StringUtils.objectToInt(DbValueConvertUtils
							.GetRowObject(row, "LENGTH")));
				}
				field.setReadOnly(field.getIsAuto());
				fields.put(field.getFieldName().toUpperCase().trim(), field);
			}
		}

		TableInfoClass tableMetadate = new TableInfoClass(fields);
		tableMetadate.setTableType(TableType.Table);
		return tableMetadate;
	}

	/**
	 * 获取视图元数据
	 * 
	 * @param viewName
	 * @return
	 */
	protected TableInfoClass getViewMetadate(String viewName) {
		String sql = " select  COL.COLUMN_ID as colorder, "
				+ " COL.COLUMN_NAME as ColumnName,"
				+ " COL.DATA_TYPE as TypeName, "
				+ " DECODE(COL.DATA_TYPE, 'NUMBER',COL.DATA_PRECISION, COL.DATA_LENGTH) as Length,"
				+ " COL.DATA_PRECISION as Preci,"
				+ " COL.DATA_SCALE as Scale,"
				+ " '' as IsIdentity,"
				+ " case when PKCOL.COLUMN_POSITION >0  then 'True'else 'False' end as isPK,"
				+ " case when COL.NULLABLE='Y'  then 'True'else 'False' end as cisNull, "
				+ " COL.DATA_DEFAULT as defaultVal, "
				+ " CCOM.COMMENTS as deText,"
				+ " COL.NUM_DISTINCT as NUM_DISTINCT "
				+ " FROM USER_TAB_COLUMNS COL,USER_COL_COMMENTS CCOM, "
				+ " ( SELECT AA.TABLE_NAME, AA.INDEX_NAME, AA.COLUMN_NAME, AA.COLUMN_POSITION"
				+ " FROM USER_IND_COLUMNS AA, USER_CONSTRAINTS BB"
				+ " WHERE BB.CONSTRAINT_TYPE = 'P'"
				+ " AND AA.TABLE_NAME = BB.TABLE_NAME"
				+ " AND AA.INDEX_NAME = BB.CONSTRAINT_NAME"
				+ " AND AA.TABLE_NAME = @viewName "
				+ " ) PKCOL WHERE COL.TABLE_NAME = CCOM.TABLE_NAME"
				+ " AND COL.COLUMN_NAME = CCOM.COLUMN_NAME"
				+ " AND COL.TABLE_NAME = @viewName "
				+ " AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+)"
				+ " AND COL.TABLE_NAME = PKCOL.TABLE_NAME(+)"
				+ " ORDER BY COL.COLUMN_ID ";

		GlobalParameter[] para = { new GlobalParameter("@viewName", viewName) };
		List<RowBase> schemaTable = null;
		schemaTable = getEntityList(RowBase.class, sql, para);

		IDataTypeConvert dataTypeConvert = new OracleDataTypeConvert();
		HashMap<String, Field> fields = new HashMap<String, Field>();

		if (schemaTable != null && schemaTable.size() > 0) {
			for (RowBase row : schemaTable) {
				Field field = new Field();
				field.setFieldName((String) DbValueConvertUtils.GetRowObject(
						row, "ColumnName"));
				field.setFieldID((Integer) DbValueConvertUtils.GetRowObject(
						row, "colorder"));
				field.setAllowNull((Boolean) DbValueConvertUtils.GetRowObject(
						row, "cisNull"));
				field.setDataType(dataTypeConvert.Convert(row.getValue(
						"TypeName").toString()));
				if (field.getDataType() == GlobalDataType.NoDefault)
					field.setCanShow(false);
				else {
					if (RowBase.isSystemField(field.getFieldName()))
						field.setCanShow(false);
					else
						field.setCanShow(true);
				}

				field.setDecimaldigits((Integer) DbValueConvertUtils
						.GetRowObject(row, "Scale"));
				field.setDefaultValue((String) DbValueConvertUtils
						.GetRowObject(row, "defaultVal"));
				field.setFieldAliasName(field.getFieldName());
				field.setIsAuto((Boolean) DbValueConvertUtils.GetRowObject(row,
						"IsIdentity"));
				field.setIsKey((Boolean) DbValueConvertUtils.GetRowObject(row,
						"isPK"));
				field.setMaxLen(StringUtils.objectToInt(DbValueConvertUtils
						.GetRowObject(row, "length")));
				field.setReadOnly(true);
				fields.put(field.getFieldName().toUpperCase().trim(), field);
			}
		}

		TableInfoClass tableMetadate = new TableInfoClass(fields);
		tableMetadate.setTableType(TableType.View);
		return tableMetadate;
	}

	@Override
	public boolean objectExist(String objName) {
		objName = objName.trim().toUpperCase();
		String sql = "select count(tname) from tab where tname = @tableName and (TABTYPE='VIEW' or TABTYPE='TABLE')";
		GlobalParameter[] para = { new GlobalParameter("@tableName", objName) };

		Object obj = getExecuteScalar(sql, para);
		long cmdresult;
		if (obj.equals(null)) {
			sql = "SELECT count(name) FROM ALL_SOURCE where name=@tableName";
			obj = getExecuteScalar(sql, para);

			if (null == obj)
				cmdresult = 0;
			else
				cmdresult = Long.parseLong(obj.toString());
		} else
			cmdresult = Long.parseLong(obj.toString());

		if (cmdresult == 0)
			return false;
		else
			return true;
	}

	@Override
	public TableType getObjectType(String objName) {
		if (StringUtils.IsNullOrSpace(objName))
			return TableType.UnDefine;

		objName = objName.toUpperCase();
		String sql = "select TABTYPE from tab where tname=@viewName";
		GlobalParameter[] para = { new GlobalParameter("@viewName", objName) };
		Object obj = getExecuteScalar(sql, para);
		if (obj == null) {
			return TableType.UnDefine;
		} else {
			String typeStr = obj.toString().trim().toUpperCase();
			if (typeStr.equals("VIEW")) {
				return TableType.View;
			} else if (typeStr.equals("TABLE")) {
				return TableType.Table;
			}
		}
		return TableType.UnDefine;
	}

	@SuppressWarnings("unchecked")
	@Override
	public String[] getObjects(TableType tableType) {
		String sql = "";
		switch (tableType) {
		case Table:
			sql = "select TNAME from tab where TABTYPE='TABLE'";
			break;
		case View:
			sql = "select TNAME from tab where TABTYPE='VIEW'";
			break;
		case UnDefine:
		default:
			break;
		}

		if (StringUtils.IsNullOrSpace(sql))
			return null;

		List<String> tabNames = new ArrayList<String>();

		DataReaderDelegate iDataReaderDelegate = new getObjectsDelegate();

		getDataReader(RowBase.class, sql, GlobalParameter.Empty,
				iDataReaderDelegate);
		tabNames = (List<String>) iDataReaderDelegate.paraObject;

		return tabNames.toArray(new String[tabNames.size()]);
	}

	private class getObjectsDelegate extends DataReaderDelegate {
		@Override
		public void invokeDataReader(IDataReader reader, Object tag) {
			List<String> tabNames = new ArrayList<String>();
			while (reader.read()) {
				tabNames.add((String) reader.getRow().getValue(
						GlobalDataType.String, "TNAME"));
			}
			this.paraObject = tabNames;
		}
	}

	@Override
	public void deleteObject(TableType tableType, String objName) {
		String sql = "";
		switch (tableType) {
		case Table:
			sql = "drop table " + objName;
			break;
		case View:
			tableType = getObjectType(objName);
			if (tableType == TableType.Table)
				sql = "drop materialized view" + objName;
			else if (tableType == TableType.View)
				sql = "drop view " + objName;
			break;
		case UnDefine:
		default:
			break;
		}

		if (!StringUtils.IsNullOrSpace(sql))
			executeSql(sql, GlobalParameter.Empty);
	}

	@Override
	public void rename(String oldTableName, String newTableName) {
		TableType tableType = getObjectType(oldTableName);
		if (tableType == TableType.Table) {
			String sql = "alter table " + oldTableName + " rename to "
					+ newTableName;
			executeSql(sql, GlobalParameter.Empty);
		}
	}

	@Override
	public void createTable(ITableInfo tableInfo, String tableName) {
		if (StringUtils.IsNullOrSpace(tableName))
			return;

		if (!objectExist(tableName)) {
			String sql = getMetadataSql(tableInfo, tableName);
			if (StringUtils.IsNullOrSpace(sql))
				return;

			Field[] autoFields = tableInfo.getAutoFields();
			executeSql(sql, GlobalParameter.Empty);
			if (autoFields.length > 0) {
				// 序列 -- 如果存在会报错
				sql = "create sequence "
						+ tableName
						+ "_SE minvalue 1 maxvalue 99999999 start with 1 increment by 1 nocache";
				executeSql(sql, GlobalParameter.Empty);
			}
		}
	}

	/**
	 * 获取元数据SQL
	 * 
	 * @param tableMetadata
	 * @param tableName
	 * @return
	 */
	protected String getMetadataSql(ITableInfo tableMetadata, String tableName) {
		if (tableMetadata.getTableType() == TableType.Table) {
			IDataTypeConvert dataTypeConvert = new OracleDataTypeConvert();

			StringBuilder sqlBuilder = new StringBuilder();
			sqlBuilder.append("Create Table " + tableName + "(");
			for (Field field : tableMetadata.getFields()) {
				sqlBuilder.append(field.getFieldName() + " ");
				if (field.getDataType() == GlobalDataType.String
						&& field.getMaxLen() > 0)
					sqlBuilder.append(dataTypeConvert.Convert(
							field.getDataType()).replace("254",
							String.valueOf(field.getMaxLen())

					)
							+ ",");
				else
					sqlBuilder.append(dataTypeConvert.Convert(field
							.getDataType()) + ",");
			}

			String sql = sqlBuilder.toString();
			// 主键
			Field[] keyFields = tableMetadata.getKeyNoAutoFields();
			if (keyFields.length != 0) {
				sqlBuilder.delete(0, sqlBuilder.length());
				sqlBuilder.append("constraint " + tableName
						+ "_primary primary key(");
				for (Field field : keyFields) {
					sqlBuilder.append(field.getFieldName() + ",");
				}
				sql += sqlBuilder.delete(sqlBuilder.length() - 1,
						sqlBuilder.length()).append(") )");
			} else
				sql = sql.substring(0, (sql.length() - 1)) + ")";

			return sql;
		}
		return "";
	}

	@Override
	public void updateTable(ITableInfo tableInfo, String tableName) {
		if (StringUtils.IsNullOrSpace(tableName))
			return;

		TableType tableType = getObjectType(tableName);
		if (tableType == TableType.Table) {
			IDataTypeConvert dataTypeConvert = new OracleDataTypeConvert();
			List<String> sqlList = new ArrayList<String>();
			for (Field field : tableInfo.getFields()) {
				String sql = "";
				String stringFieldType = "";

				if (field.getDataType() == GlobalDataType.String)
					stringFieldType = dataTypeConvert.Convert(
							field.getDataType()).replace("254",
							String.valueOf(field.getMaxLen())

					);
				else
					stringFieldType = dataTypeConvert.Convert(field
							.getDataType());

				if (fieldExist(tableName, field.getFieldName()))
					sql = "ALTER TABLE " + tableName + " alter column "
							+ field.getFieldName() + " " + stringFieldType;
				else
					sql = "ALTER TABLE " + tableName + " add column "
							+ field.getFieldName() + " " + stringFieldType;

				sqlList.add(sql);
			}
			for (String sql : sqlList) {
				this.executeSql(sql, GlobalParameter.Empty);
			}
		}
	}

	@Override
	public void createOrUpdateView(String viewName, String selectSql,
			boolean isStatic) {
		viewName = viewName.trim().toUpperCase();
		deleteObject(TableType.View, viewName);

		String sql = "";
		if (isStatic)
			sql = "CREATE materialized view "
					+ viewName
					+ " build immediate refresh force on demand START WITH SYSDATE NEXT SYSDATE + 1/12 as "
					+ selectSql;
		else
			sql = "CREATE View " + viewName + " as " + selectSql;
		executeSql(sql, GlobalParameter.Empty);
	}
}